package qimao.com.edu.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.List;


import qimao.com.edu.DTO.BookDTO;
import qimao.com.edu.DTO.ChapterDTO;
import qimao.com.edu.DTO.WorkQueryDTO;
import qimao.com.edu.VO.BookVO;
import qimao.com.edu.dao.BaseDao;
import qimao.com.edu.dao.BookDao;
import qimao.com.edu.entity.Chapters;
import qimao.com.edu.entity.Works;

public class BookDaoImpl implements BookDao {

	@Override
	public List<BookDTO> selectBook(String str,int pageNum,int pageSize) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<BookDTO> books = new ArrayList();
		// w 作品表/p 主角/c 类型
		String sql = "SELECT w.work_id,w.title,w.cover,w.description,w.is_finished,a.pen_name AS author_name," +
				"MAX(c.minor_name) AS category_name, " +
				"GROUP_CONCAT(DISTINCT p.NAME SEPARATOR ' ') AS protagonist_names " +
		        "FROM works w " +
		        "INNER JOIN author a ON w.user_id = a.author_id " +
		        "LEFT JOIN category_minor c ON w.category_id = c.parent_id " +
		        "LEFT JOIN protagonist p ON w.work_id = p.work_id " +
		        "WHERE w.status = 1 " +  // 只查询审核通过的作品
		        "AND (w.title LIKE CONCAT('%', ?, '%') OR p.NAME LIKE CONCAT('%', ?, '%') OR a.pen_name LIKE CONCAT('%', ?, '%')) " +
		        "GROUP BY w.work_id " +
		        "LIMIT ?,?;";
		       
		try {
			con = BaseDao.getCon();	
			ps = con.prepareStatement(sql);
			if (str != null) {
				ps.setString(1,str);
				ps.setString(2,str);
				ps.setString(3,str);
				ps.setInt(4,pageNum);
				ps.setInt(5,pageSize);
				System.out.println(ps);
			}else {
				return null;
			}
			ps.setInt(4,(pageNum-1)*pageSize);
			ps.setInt(5,pageSize);
			rs = ps.executeQuery();
			while (rs.next()) {
				BookDTO b = new BookDTO();
				b.setBookId(rs.getInt("work_id"));
				b.setBookName(rs.getString("title"));
				b.setBookImg(rs.getString("cover"));
				b.setBookType(rs.getString("category_name"));
				int Is_finished = rs.getInt("is_finished");
				if(Is_finished == 0) {
					b.setIs_finished("未完结");
				}else {
					b.setIs_finished("已完结");
				}
			    b.setBookIntroduction(rs.getString("description"));
				b.setProtagonist(rs.getString("protagonist_names"));
			    b.setBookWriter(rs.getString("author_name"));
			    books.add(b);
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			BaseDao.closeAll(con, ps, rs);
		}
		return books;
	}

	@Override
	public int selectBookTotalRows(String str, int pageNum, int pageSize) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		List<BookDTO> books = new ArrayList();
		int totalRows = 0;
		// w 作品表/p 主角/c 类型
		String sql = "SELECT w.work_id,w.title,w.cover,w.description,w.is_finished,a.pen_name AS author_name," +
				"MAX(c.minor_name) AS category_name, " +
				"GROUP_CONCAT(DISTINCT p.NAME SEPARATOR ' ') AS protagonist_names " +
		        "FROM works w " +
		        "INNER JOIN author a ON w.user_id = a.author_id " +
		        "LEFT JOIN category_minor c ON w.category_id = c.parent_id " +
		        "LEFT JOIN protagonist p ON w.work_id = p.work_id " +
		        "WHERE w.status = 1 " +  // 只查询审核通过的作品
		        "AND (w.title LIKE CONCAT('%', ?, '%') OR p.NAME LIKE CONCAT('%', ?, '%') OR a.pen_name LIKE CONCAT('%', ?, '%')) " +
		        "GROUP BY w.work_id " +
		        "LIMIT ?,?;";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			if (str != null) {
				ps.setString(1,str);
				ps.setString(2,str);
				ps.setString(3,str);
			}else {
				return 0;
			}
			ps.setInt(4,(pageNum-1)*pageSize);
			ps.setInt(5,pageSize);
			rs = ps.executeQuery();
			if(rs.next()) {
				totalRows = rs.getInt(1);
			}
		}catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			BaseDao.closeAll(con, ps, rs);
		}
		return totalRows;
	}

	@Override
	public Chapters selectChapter(Integer bookId, Integer chapterNum) {
		Connection con = null;
		PreparedStatement ps = null;
		ResultSet rs = null;
		Chapters c = null;
		String sql = "SELECT * FROM chapters WHERE work_id = ? AND chapter_id = ?";
		try {
			con = BaseDao.getCon();
			ps = con.prepareStatement(sql);
			ps.setInt(1, bookId);
			ps.setInt(2, chapterNum);
			rs = ps.executeQuery();
			if(rs.next()) {
				c = new Chapters();
				c.setWorkId(rs.getInt("work_id"));
				c.setChapterId(rs.getInt("chapter_id"));
				c.setChapterTitle(rs.getString("chapter_title"));
				c.setContentPath(rs.getString("content_path"));
				
				// 从数据库获取datetime类型并转换为LocalDateTime
				Timestamp timestamp = rs.getTimestamp("update_time");
			    c.setUpdate_time(timestamp.toLocalDateTime());
				c.setWordCount(rs.getInt("word_count"));
				
			}
		} catch (Exception e) {
			// TODO: handle exception
		} finally {
			BaseDao.closeAll(con, ps, rs);
		}
		return c;
	}

	@Override
	public int insertBook(Integer userId, Works book) {
		
		return 0;
	}

	@Override
	public List<BookDTO> selectWorksByConditions(WorkQueryDTO workQueryDTO) {
	    Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    List<BookDTO> books = new ArrayList<>();
	    
	    // 构建SQL语句
	    StringBuilder sql = new StringBuilder();
	    sql.append("SELECT w.work_id AS bookId, w.title AS bookName, w.cover AS bookImg, ");
	    sql.append("minor.minor_name AS bookType, w.description AS bookIntroduction, ");
	    sql.append("a.pen_name AS bookWriter, w.is_finished, w.update_time, w.word_count ");
	    sql.append("FROM works w ");
	    sql.append("INNER JOIN author a ON w.user_id = a.author_id ");
	    sql.append("LEFT JOIN category_minor minor ON w.category_id = minor.minor_id ");
	    sql.append("LEFT JOIN category_major major ON minor.parent_id = major.major_id ");
	    sql.append("WHERE w.status = ? ");
	    
	    // 参数列表
	    List<Object> params = new ArrayList<>();
	    params.add(workQueryDTO.getStatus());
	    
	    // 添加动态查询条件
	    if (workQueryDTO.getGender() != null && !workQueryDTO.getGender().isEmpty()) {
	        sql.append("AND a.gender = ? ");
	        params.add(workQueryDTO.getGender());
	    }
	    
	    if (workQueryDTO.getMajorCategoryId() != null) {
	        sql.append("AND major.major_id = ? ");
	        params.add(workQueryDTO.getMajorCategoryId());
	    }
	    
	    if (workQueryDTO.getMinorCategoryId() != null) {
	        sql.append("AND w.category_id = ? ");
	        params.add(workQueryDTO.getMinorCategoryId());
	    }
	    
	    if (workQueryDTO.getWordCountMin() != null && workQueryDTO.getWordCountMax() != null) {
	        sql.append("AND w.word_count BETWEEN ? AND ? ");
	        params.add(workQueryDTO.getWordCountMin());
	        params.add(workQueryDTO.getWordCountMax());
	    }
	    
	    if (workQueryDTO.getUpdateTime() != null) {
	        sql.append("AND w.update_time >= ? ");
	        params.add(Timestamp.valueOf(workQueryDTO.getUpdateTime()));
	    }
	    
	    if (workQueryDTO.getIsFinished() != null) {
	        sql.append("AND w.is_finished = ? ");
	        params.add(workQueryDTO.getIsFinished());
	    }
	    
	    // 排序
	    sql.append("ORDER BY w.update_time DESC ");
	    sql.append("LIMIT ?, ?");
	    
	    try {
	        con = BaseDao.getCon();
	        ps = con.prepareStatement(sql.toString());
	        
	        // 设置参数
	        for (int i = 0; i < params.size(); i++) {
	            ps.setObject(i + 1, params.get(i));
	        }
	        
	        // 设置分页参数
	        int offset = (workQueryDTO.getPageNum() - 1) * workQueryDTO.getPageSize();
	        ps.setInt(params.size() + 1, offset);
	        ps.setInt(params.size() + 2, workQueryDTO.getPageSize());
	        
	        System.out.println("执行SQL: " + ps);
	        rs = ps.executeQuery();
	        
	        while (rs.next()) {
	            BookDTO book = new BookDTO();
	            book.setBookId(rs.getInt("bookId"));
	            book.setBookName(rs.getString("bookName"));
	            book.setBookImg(rs.getString("bookImg"));
	            book.setBookType(rs.getString("bookType"));
	            book.setBookIntroduction(rs.getString("bookIntroduction"));
	            book.setBookWriter(rs.getString("bookWriter"));
	            
	            // 处理是否完结字段
	            int isFinished = rs.getInt("is_finished");
	            book.setIs_finished(isFinished == 1 ? "已完结" : "连载中");
	            book.setBookState(isFinished == 1 ? "已完结" : "连载中");
	            
	            // 处理更新时间
	            Timestamp updateTime = rs.getTimestamp("update_time");
	            if (updateTime != null) {
	                book.setUpdateTime(updateTime.toLocalDateTime());
	            }
	            
	            // 处理字数
	            book.setWordCount(rs.getInt("word_count"));
	            
	            books.add(book);
	        }
	    } catch (SQLException e) {
	        System.err.println("查询作品失败: " + e.getMessage());
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(con, ps, rs);
	    }
	    
	    return books;
	}

	@Override
	public int selectWorksByConditionsTotalRows(WorkQueryDTO workQueryDTO) {
		Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    int totalRows = 0;
	    List<BookDTO> books = new ArrayList<>();
	    
	    // 构建SQL语句
	    StringBuilder sql = new StringBuilder();
	    sql.append("SELECT w.work_id AS bookId, w.title AS bookName, w.cover AS bookImg, ");
	    sql.append("minor.minor_name AS bookType, w.description AS bookIntroduction, ");
	    sql.append("a.pen_name AS bookWriter, w.is_finished, w.update_time, w.word_count ");
	    sql.append("FROM works w ");
	    sql.append("INNER JOIN author a ON w.user_id = a.author_id ");
	    sql.append("LEFT JOIN category_minor minor ON w.category_id = minor.minor_id ");
	    sql.append("LEFT JOIN category_major major ON minor.parent_id = major.major_id ");
	    sql.append("WHERE w.status = ? ");
	    
	    // 参数列表
	    List<Object> params = new ArrayList<>();
	    params.add(workQueryDTO.getStatus());
	    
	    // 添加动态查询条件
	    if (workQueryDTO.getGender() != null && !workQueryDTO.getGender().isEmpty()) {
	        sql.append("AND a.gender = ? ");
	        params.add(workQueryDTO.getGender());
	    }
	    
	    if (workQueryDTO.getMajorCategoryId() != null) {
	        sql.append("AND major.major_id = ? ");
	        params.add(workQueryDTO.getMajorCategoryId());
	    }
	    
	    if (workQueryDTO.getMinorCategoryId() != null) {
	        sql.append("AND w.category_id = ? ");
	        params.add(workQueryDTO.getMinorCategoryId());
	    }
	    
	    if (workQueryDTO.getWordCountMin() != null && workQueryDTO.getWordCountMax() != null) {
	        sql.append("AND w.word_count BETWEEN ? AND ? ");
	        params.add(workQueryDTO.getWordCountMin());
	        params.add(workQueryDTO.getWordCountMax());
	    }
	    
	    if (workQueryDTO.getUpdateTime() != null) {
	        sql.append("AND w.update_time >= ? ");
	        params.add(Timestamp.valueOf(workQueryDTO.getUpdateTime()));
	    }
	    
	    if (workQueryDTO.getIsFinished() != null) {
	        sql.append("AND w.is_finished = ? ");
	        params.add(workQueryDTO.getIsFinished());
	    }
	    
	    // 排序
	    sql.append("ORDER BY w.update_time DESC ");
	    sql.append("LIMIT ?, ?");
	    
	    try {
	        con = BaseDao.getCon();
	        ps = con.prepareStatement(sql.toString());
	        
	        // 设置参数
	        for (int i = 0; i < params.size(); i++) {
	            ps.setObject(i + 1, params.get(i));
	        }
	        
	        // 设置分页参数
	        int offset = (workQueryDTO.getPageNum() - 1) * workQueryDTO.getPageSize();
	        ps.setInt(params.size() + 1, offset);
	        ps.setInt(params.size() + 2, workQueryDTO.getPageSize());
	        
	        System.out.println("执行SQL: " + ps);
	        rs = ps.executeQuery();
	        if(rs.next()) {
				totalRows = rs.getInt(1);
			}
	    } catch (SQLException e) {
	        System.err.println("查询作品失败: " + e.getMessage());
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(con, ps, rs);
	    }
	    
	    return totalRows;
	}
	
	@Override
	public BookDTO selectBookDTO(int workId) {
	    Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    BookDTO works = null;
	    
	    String sql = "SELECT " +
	            "w.work_id, w.user_id, w.title, w.category_id, w.cover, " +
	            "w.description, w.chapter_count, w.word_count, a.pen_name AS author_name, " +
	            "w.is_original, w.vip_type, w.status, w.is_finished, " +
	            "w.publish_time, w.update_time, w.favorites_count, " +
	            "w.read_count, w.popularity, m.minor_name AS category_name," +
	            "maj.major_name AS major_category_name, " +
	            "GROUP_CONCAT(p.`name` SEPARATOR ', ') AS protagonist_names " +
	            "FROM works w " +
	            "LEFT JOIN `category_minor` m ON w.category_id = m.minor_id " +
	            "LEFT JOIN category_major maj ON m.parent_id = maj.major_id " +
	            "LEFT JOIN author a ON w.user_id = a.author_id " +
	            "LEFT JOIN protagonist p ON w.work_id = p.work_id " +
	            "WHERE w.work_id = ? " +
	            "GROUP BY w.work_id";
	    
	    try {
	        con = BaseDao.getCon();
	        ps = con.prepareStatement(sql);
	        ps.setInt(1, workId);
	        rs = ps.executeQuery();
	        
	        if (rs.next()) {
	            works = new BookDTO();
	            works.setBookId(rs.getInt("work_id"));
	            works.setBookWriterId(rs.getInt("user_id"));
	            works.setBookWriter(rs.getString("author_name"));
	            works.setBookName(rs.getString("title"));
	            works.setBookType(rs.getString("category_name"));
	            works.setBookImg(rs.getString("cover"));
	            works.setBookIntroduction(rs.getString("description"));
	            works.setChapterId(rs.getInt("chapter_count"));
	            works.setWordCount(rs.getInt("word_count"));
	            works.setProtagonist(rs.getString("protagonist_names"));
	            works.setIs_finished(rs.getString("is_finished"));
	            works.setBookState(rs.getString("status"));
	            works.setBookTypeMajor(rs.getString("major_category_name"));
	            
	            // 处理时间类型
	            Timestamp updateTime = rs.getTimestamp("update_time");
	            if (updateTime != null) {
	                works.setUpdateTime(updateTime.toLocalDateTime());
	            }
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(con, ps, rs);
	    }
	    return works;
	}

	@Override
	public List<BookVO> searchAll(Integer pageNum, Integer pageSize) {
	    Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    List<BookVO> resultList = new ArrayList<>();
	    
	    String sql = "SELECT " +
	            "w.work_id, " +
	            "w.user_id, " +
	            "a.pen_name AS author_name, " +
	            "w.title, " +
	            "w.category_id, " +
	            "cm.minor_name AS category_name, " + 
	            "w.cover, " +
	            "w.description, " +
	            "w.chapter_count, " +
	            "w.word_count, " +
	            "w.is_original, " +
	            "w.status, " +
	            "w.is_finished, " +
	            "w.publish_time, " +
	            "w.update_time, " +
	            "w.favorites_count, " +
	            "w.read_count, " +
	            "w.popularity " +
	            "FROM works w " +
	            "JOIN author a ON w.user_id = a.author_id " +
	            "JOIN category_minor cm ON w.category_id = cm.minor_id " + 
	            "ORDER BY w.update_time DESC " +
	            "LIMIT ? OFFSET ?";
	    
	    try {
	        con = BaseDao.getCon();
	        ps = con.prepareStatement(sql);
	        // 设置分页参数
	        ps.setInt(1, pageSize);
	        ps.setInt(2, (pageNum - 1) * pageSize);
	        
	        rs = ps.executeQuery();
	        
	        while (rs.next()) { // 使用while循环处理所有结果
	            BookVO bookVO = new BookVO();
	            bookVO.setBookId(rs.getInt("work_id"));
	            bookVO.setBookIntroduction(rs.getString("description"));
	            bookVO.setBookImg(rs.getString("cover"));
	            bookVO.setBookName(rs.getString("title"));
	            
	            // 从is_finished列获取状态值
	            String stateStr = rs.getString("is_finished");
	            String displayState = "未知状态"; // 默认值
	            
	            if(stateStr != null) {
	                switch(stateStr) {
	                    case "0":
	                        displayState = "连载中";
	                        break;
	                    case "1":
	                        displayState = "已完结";
	                        break;
	                    case "2":
	                        displayState = "已下架";
	                        break;
	                }
	            }
	            
	            // 设置转换后的状态文本
	            bookVO.setBookState(displayState);
	            
	            // 设置分类名称
	            bookVO.setBookType(rs.getString("category_name"));
	            
	            bookVO.setBookWriter(rs.getString("author_name"));
	            resultList.add(bookVO); 
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(con, ps, rs);
	    }
	    
	    return resultList;
	}  

	@Override
	public int searchAllTotalRows() {
	    Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    int totalRows = 0;
	    
	    try {
	        con = BaseDao.getCon();
	        String sql = "SELECT COUNT(*) FROM works";
	        ps = con.prepareStatement(sql);
	        
	        rs = ps.executeQuery();
	        if (rs.next()) {
	            totalRows = rs.getInt(1);
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(con, ps, rs);
	    }
	    
	    return totalRows;
	}

	@Override
	public Works selectWorks(int workId) {
	    Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    Works works = null;
	    
	    String sql = "SELECT " +
	                 "work_id, user_id, title, category_id, cover, " +
	                 "description, chapter_count, word_count, " +
	                 "is_original, vip_type, status, is_finished, " +
	                 "publish_time, update_time, favorites_count, " +
	                 "read_count, popularity " +
	                 "FROM works " +
	                 "WHERE work_id = ?";
	    
	    try {
	        con = BaseDao.getCon();
	        ps = con.prepareStatement(sql);
	        ps.setInt(1, workId);
	        rs = ps.executeQuery();
	        
	        if (rs.next()) {
	            works = new Works();
	            works.setWorkId(rs.getInt("work_id"));
	            works.setAuthor(rs.getInt("user_id"));
	            works.setTitle(rs.getString("title"));
	            works.setCategory(rs.getInt("category_id"));
	            works.setCover(rs.getString("cover"));
	            works.setDescription(rs.getString("description"));
	            works.setChapterCount(rs.getInt("chapter_count"));
	            works.setWordCount(rs.getInt("word_count"));
	            works.setIsOriginal(rs.getInt("is_original"));
	            works.setVipType(rs.getInt("vip_type"));
	            works.setStatus(rs.getInt("status"));
	            works.setState(rs.getInt("is_finished"));
	            
	            // 处理时间类型
	            Timestamp publishTime = rs.getTimestamp("publish_time");
	            if (publishTime != null) {
	                works.setPublishTime(publishTime.toLocalDateTime());
	            }
	            
	            Timestamp updateTime = rs.getTimestamp("update_time");
	            if (updateTime != null) {
	                works.setUpdateTime(updateTime.toLocalDateTime());
	            }
	            
	            works.setFavoritesCount(rs.getInt("favorites_count"));
	            works.setReadCount(rs.getInt("read_count"));
	            works.setPopularity(rs.getFloat("popularity"));
	        }
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(con, ps, rs);
	    }
	    
	    return works;
	}
	
	@Override
	public int insertBook(Works book) {
	    Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    int result = 0;
	    
	    String sql = "INSERT INTO works (" +
	                 "user_id, title, cover, " +
	                 "description,is_original,category_id, " +
	                 "`status`,publish_time ) " +
	                 "VALUES (?, ?, ?, ?, ?, 0, 1, now())";
	    
	    try {
	        con = BaseDao.getCon();
	        ps = con.prepareStatement(sql);
	        
	        // 设置参数
	        ps.setInt(1, book.getAuthor());
	        ps.setString(2, book.getTitle());
	        ps.setString(3, book.getCover());
	        ps.setString(4, book.getDescription());
	        ps.setInt(5, book.getIsOriginal());
	        // 执行插入
	        result = ps.executeUpdate();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(con, ps, rs);
	    }
	    return result;
	}

	@Override
	public int deleteWork(int workId) {
		Connection con = null;
	    PreparedStatement ps = null;
	    ResultSet rs = null;
	    int result = 0;
	    
	    String sql = "DELETE FROM works WHERE work_id = ?";
	    
	    try {
	        con = BaseDao.getCon();
	        ps = con.prepareStatement(sql);
	        
	        // 设置参数
	        ps.setInt(1, workId);
	        // 执行插入
	        result = ps.executeUpdate();
	    } catch (SQLException e) {
	        e.printStackTrace();
	    } finally {
	        BaseDao.closeAll(con, ps, rs);
	    }
	    return result;
	}
}
